﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_BGM_ProjectOperation : IDAL_BGM_ProjectOperation
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        //

        public IList<M_BGM_ProjectOperation> GetFull(Nullable<int> bgm_source_seq, Nullable<int> bgm_year
            , Nullable<int> job_site_seq, Nullable<int> bgm_proj_seq
            , Nullable<DateTime> use_date, bool IsUse, bool ApproveOnly)
        {
            string strSQL;
            string strFilter = "";
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperation> infoList = new List<M_BGM_ProjectOperation>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@delete_record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@approve_record_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@bgm_year", bgm_year);
                dbManager.AddParameters(4, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(5, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(6, "@use_date", use_date);

                if (ApproveOnly)
                {
                    strFilter = " WHERE pj.record_status = @approve_record_status";
                }
                else
                {
                    strFilter = " WHERE pj.record_status not like @delete_record_status";
                }

                strSQL = "SELECT pj.bgm_proj_oper_seq, pj.bgm_proj_oper_code"
                    + ", pj.bgm_proj_oper_name"
                    + ", pj.bgm_proj_oper_descr, pj.bgm_proj_oper_date"
                    + ", pj.start_date, pj.end_date, pj.bgm_year, pj.bgm_proj_seq"
                    + ", pj.emp_seq, pj.job_site_seq, pj.is_use, pj.attend_count"

                    + ", pjexp.amount, rec.bgm_proj_name, j.job_site_name"
                    + ", concat(np.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"

                    + ", pj.record_status"
                    + ", case when pj.is_use = '0' or pj.record_status = 'C' then 'ปิดโครงการ'"
                        + " else rs.record_status_name"
                        + " end as record_status_name"
                    + " FROM bgm_project_operation pj"
                    + " LEFT JOIN (SELECT bgm_proj_oper_seq, sum(coalesce(amount, 0)) as amount"
                        + " FROM bgm_project_operation_expense"
                        + " WHERE record_status not like @delete_record_status"
                        + (bgm_source_seq != null ? " AND bgm_source_seq = @bgm_source_seq" : "")
                        + " GROUP BY bgm_proj_oper_seq"
                        + ") pjexp ON pj.bgm_proj_oper_seq = pjexp.bgm_proj_oper_seq"
                    + " LEFT JOIN bgm_project rec ON pj.bgm_proj_seq = rec.bgm_proj_seq"

                    + " LEFT JOIN hrm_mp_job_sites j ON pj.job_site_seq = j.job_site_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON pj.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np ON emp.name_prefix_seq = np.name_prefix_seq"

                    + " LEFT JOIN sys_record_status rs ON pj.record_status = rs.record_status"
                    
                    + strFilter
                    + (bgm_year != null ? " AND pj.bgm_year = @bgm_year" : "")
                    + (job_site_seq != null ? " AND pj.job_site_seq = @job_site_seq" : "")
                    + (bgm_proj_seq != null ? " AND pj.bgm_proj_seq = @bgm_proj_seq" : "")
                    + (IsUse ? " AND pj.is_use = '1'" : "")
                    + (use_date != null ? " AND @use_date > pj.start_date AND @use_date < pj.end_date" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperation info = new M_BGM_ProjectOperation();
                        info.bgm_proj_oper_seq = Convert.ToInt32(drc[i]["bgm_proj_oper_seq"]);
                        info.bgm_proj_oper_code = drc[i]["bgm_proj_oper_code"].ToString();
                        info.bgm_proj_oper_name = drc[i]["bgm_proj_oper_name"].ToString();
                        info.bgm_proj_oper_descr = drc[i]["bgm_proj_oper_descr"].ToString();
                        info.bgm_proj_oper_date = cn.ConvertNullDateTime(drc[i]["bgm_proj_oper_date"]);
                        info.start_date = cn.ConvertNullDateTime(drc[i]["start_date"]);
                        info.end_date = cn.ConvertNullDateTime(drc[i]["end_date"]);
                        info.bgm_year = cn.ConvertNullInt(drc[i]["bgm_year"]);
                        info.bgm_proj_seq = cn.ConvertNullInt(drc[i]["bgm_proj_seq"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.job_site_seq = cn.ConvertNullInt(drc[i]["job_site_seq"]);
                        info.sum_amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.attend_count = cn.ConvertNullInt(drc[i]["attend_count"]);

                        M_BGM_Project subInfo1 = new M_BGM_Project();
                        M_HRM_MP_Jobsites subInfo2 = new M_HRM_MP_Jobsites();
                        M_HRM_PS_Employees subInfo3 = new M_HRM_PS_Employees();

                        subInfo1.bgm_proj_name = drc[i]["bgm_proj_name"].ToString();
                        subInfo2.job_site_name = drc[i]["job_site_name"].ToString();
                        subInfo3.emp_name = drc[i]["emp_name"].ToString();

                        info.m_bgm_project = subInfo1;
                        info.m_hrm_mp_jobsites = subInfo2;
                        info.m_hrm_ps_employees = subInfo3;

                        info.record_status = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectOperation> GetList(Nullable<int> bgm_source_seq, Nullable<int> bgm_year
            , Nullable<int> job_site_seq, Nullable<int> bgm_proj_seq
            , Nullable<DateTime> use_date, bool IsUse, bool ApproveOnly)
        {
            string strSQL;
            string strFilter = "";
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperation> infoList = new List<M_BGM_ProjectOperation>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@delete_record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@approve_record_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@bgm_year", bgm_year);
                dbManager.AddParameters(4, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(5, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(6, "@use_date", use_date);

                if (ApproveOnly)
                {
                    strFilter = " WHERE pj.record_status = @approve_record_status";
                }
                else
                {
                    strFilter = " WHERE pj.record_status not like @delete_record_status";
                }

                strSQL = "SELECT pj.bgm_proj_oper_seq "
                    + ", CONCAT(coalesce(pj.bgm_proj_oper_code, ''), ' : ', coalesce(pj.bgm_proj_oper_name, '')"
                        + ", '[', cast(format(coalesce(pjexp.amount, 0), '#,##0.00') as varchar), ' บาท]') AS bgm_proj_oper_name"
                    + ", pjexp.amount"
                    + " FROM bgm_project_operation pj"
                    + " INNER JOIN (SELECT ope.bgm_proj_oper_seq, SUM (COALESCE(ope.amount, 0)) AS amount"
                        + " FROM bgm_project_operation_expense ope"
                        + " left join bgm_project_expense pje on ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq"
                        + " WHERE ope.record_status not like @delete_record_status"
                        + (bgm_source_seq != null ? " AND bgm_source_seq = @bgm_source_seq" : "")
                        + " GROUP BY ope.bgm_proj_oper_seq"
                        + ") pjexp ON pj.bgm_proj_oper_seq = pjexp.bgm_proj_oper_seq"
                    + " LEFT JOIN bgm_project rec ON pj.bgm_proj_seq = rec.bgm_proj_seq"

                    + strFilter
                    + (bgm_year != null ? " AND pj.bgm_year = @bgm_year" : "")
                    + (job_site_seq != null ? " AND pj.job_site_seq = @job_site_seq" : "")
                    + (bgm_proj_seq != null ? " AND pj.bgm_proj_seq = @bgm_proj_seq" : "")
                    + (IsUse ? " AND pj.is_use = '1'" : "")
                    + (use_date != null ? " AND @use_date > pj.start_date AND @use_date < pj.end_date" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperation info = new M_BGM_ProjectOperation();
                        info.bgm_proj_oper_seq = Convert.ToInt32(drc[i]["bgm_proj_oper_seq"]);
                        info.bgm_proj_oper_name = drc[i]["bgm_proj_oper_name"].ToString();
                        info.sum_amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectOperation> GetListByBgmOptionSeq(Nullable<int> bgm_year
            , Nullable<int> bgm_option_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperation> infoList = new List<M_BGM_ProjectOperation>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@approve_record_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@bgm_option_seq", bgm_option_seq);

                strSQL = "SELECT distinct pj.bgm_proj_oper_seq"
                    + ", CONCAT(coalesce(pj.bgm_proj_oper_code, ''), ' : ', coalesce(pj.bgm_proj_oper_name, '')) AS bgm_proj_oper_name"
                    //+ ", coalesce(pjexp.amount, 0) AS amount"
                    //+ ", pj.bgm_proj_oper_date, pj.start_date, pj.end_date, pj.bgm_year"
                    //+ ", pj.job_site_seq, j.job_site_name, j.job_site_name_short"
                    + ", pj.record_status"
                    + ", case when pj.is_use = '0' then 'ปิดโครงการ'"
                        + " else rs.record_status_name"
                        + " end as record_status_name"
                    + " FROM bgm_project_operation pj"
                    //+ " INNER JOIN (SELECT bgm_proj_oper_seq, sum(coalesce(amount, 0)) as amount"
                    //    + " FROM bgm_project_operation_expense"
                    //    + " WHERE record_status not like @delete_record_status"
                    //    + " GROUP BY bgm_proj_oper_seq"
                    //    + ") pjexp ON pj.bgm_proj_oper_seq = pjexp.bgm_proj_oper_seq"
                    //+ " LEFT JOIN bgm_project p ON pj.bgm_proj_seq = p.bgm_proj_seq"
                    + " INNER JOIN bgm_project_option popt ON pj.bgm_proj_seq = popt.bgm_proj_seq"
                        + " AND bgm_option_seq = @bgm_option_seq"
                    //+ " LEFT JOIN hrm_mp_job_sites j ON pj.job_site_seq = j.job_site_seq"
                    + " LEFT JOIN sys_record_status rs ON pj.record_status = rs.record_status"
                    + " WHERE pj.record_status not like @delete_record_status"
                    + (bgm_year != null ? " AND pj.bgm_year = @bgm_year" : "");


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperation info = new M_BGM_ProjectOperation();
                        info.bgm_proj_oper_seq = Convert.ToInt32(drc[i]["bgm_proj_oper_seq"]);
                        info.bgm_proj_oper_name = drc[i]["bgm_proj_oper_name"].ToString();
                        //info.sum_amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        //info.bgm_proj_oper_date = cn.ConvertNullDateTime(drc[i]["bgm_proj_oper_date"]);
                        //info.start_date = cn.ConvertNullDateTime(drc[i]["start_date"]);
                        //info.end_date = cn.ConvertNullDateTime(drc[i]["end_date"]);
                        //info.bgm_year = cn.ConvertNullInt(drc[i]["bgm_year"]);
                        //info.job_site_seq = cn.ConvertNullInt(drc[i]["job_site_seq"]);
                        info.record_status = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectOperation> GetProjectListByEmpSeq(Nullable<int> bgm_year, Nullable<int> bgm_source_seq, Nullable<int> emp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectOperation> infoList = new List<M_BGM_ProjectOperation>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@approve_record_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@emp_seq", emp_seq);
                dbManager.AddParameters(4, "@bgm_year", bgm_year);

                strSQL = "SELECT distinct pj.bgm_proj_oper_seq, rec.bgm_proj_name, pjexp.amount"
                    + ", CONCAT(coalesce(pj.bgm_proj_oper_code, ''), ' : ', coalesce(pj.bgm_proj_oper_name, '')"
                        + ", '[', cast(format(coalesce(pjexp.amount, 0), '#,##0.00') as varchar), ' บาท]') AS bgm_proj_oper_name"
                    + " FROM bgm_project_operation pj"
                    + " INNER JOIN (SELECT opexp.bgm_proj_oper_seq, sum(coalesce(opexp.amount, 0)) as amount"
                        + " FROM bgm_project_operation_expense opexp"
                        + " LEFT JOIN bgm_project_expense pjexp ON opexp.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
                        + " WHERE opexp.record_status not like @delete_record_status"
                        + (bgm_source_seq != null ? " AND pjexp.bgm_source_seq = @bgm_source_seq" : "")
                        + " GROUP BY opexp.bgm_proj_oper_seq"
                        + ") pjexp ON pj.bgm_proj_oper_seq = pjexp.bgm_proj_oper_seq"
                    + " LEFT JOIN bgm_project rec ON pj.bgm_proj_seq = rec.bgm_proj_seq"
                    + " LEFT JOIN bgm_project_operation_person pjper ON pj.bgm_proj_oper_seq = pjper.bgm_proj_oper_seq"

                    + " WHERE pj.record_status = @approve_record_status"
                    + " AND pj.is_use = '1'"
                    + (emp_seq != null ? " AND pjper.emp_seq = @emp_seq" : "")
                    + (bgm_year != null ? " AND pj.bgm_year = @bgm_year" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectOperation info = new M_BGM_ProjectOperation();
                        info.bgm_proj_oper_seq = Convert.ToInt32(drc[i]["bgm_proj_oper_seq"]);
                        info.bgm_proj_oper_name = drc[i]["bgm_proj_oper_name"].ToString();
                        info.sum_amount = cn.ConvertNullDouble(drc[i]["amount"]);

                        M_BGM_Project SubInfo = new M_BGM_Project();
                        SubInfo.bgm_proj_name = drc[i]["bgm_proj_name"].ToString();
                        info.m_bgm_project = SubInfo;
                        
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_BGM_ProjectOperation GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_BGM_ProjectOperation info = new M_BGM_ProjectOperation();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_record_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT pj.bgm_proj_oper_seq, pj.bgm_proj_oper_code"
                    + ", pj.bgm_proj_oper_name, pj.bgm_proj_oper_date, pj.bgm_proj_oper_descr"
                    + ", pj.start_date, pj.end_date, pj.bgm_year, pj.bgm_proj_seq"
                    + ", pj.emp_seq, pj.job_site_seq, pj.attend_count"

                    + ", pjexp.amount, rec.bgm_proj_name, j.job_site_name, j.job_site_name_short"
                    + ", concat(coalesce(np.prefix_name, ''), emp.first_name, ' ', emp.last_name) as emp_name"

                    + ", pj.record_status"
                    + ", case when pj.is_use = '0' then 'ปิดโครงการ'"
                        + " else rs.record_status_name"
                        + " end as record_status_name"

                    + " FROM bgm_project_operation pj"
                    + " LEFT JOIN (SELECT bgm_proj_oper_seq, sum(coalesce(amount, 0)) as amount"
                        + " FROM bgm_project_operation_expense"
                        + " WHERE record_status not like @delete_record_status"
                        + " GROUP BY bgm_proj_oper_seq"
                        + ") pjexp ON pj.bgm_proj_oper_seq = pjexp.bgm_proj_oper_seq"
                    + " LEFT JOIN bgm_project rec ON pj.bgm_proj_seq = rec.bgm_proj_seq"
                    + " LEFT JOIN hrm_mp_job_sites j ON pj.job_site_seq = j.job_site_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON pj.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np ON emp.name_prefix_seq = np.name_prefix_seq"

                    + " LEFT JOIN sys_record_status rs ON pj.record_status = rs.record_status"

                    + " WHERE pj.bgm_proj_oper_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.bgm_proj_oper_seq = Convert.ToInt32(drc[0]["bgm_proj_oper_seq"]);
                    info.bgm_proj_oper_code = drc[0]["bgm_proj_oper_code"].ToString();
                    info.bgm_proj_oper_name = drc[0]["bgm_proj_oper_name"].ToString();
                    info.bgm_proj_oper_descr = drc[0]["bgm_proj_oper_descr"].ToString();
                    info.bgm_proj_oper_date = cn.ConvertNullDateTime(drc[0]["bgm_proj_oper_date"]);
                    info.start_date = cn.ConvertNullDateTime(drc[0]["start_date"]);
                    info.end_date = cn.ConvertNullDateTime(drc[0]["end_date"]);
                    info.bgm_year = cn.ConvertNullInt(drc[0]["bgm_year"]);
                    info.bgm_proj_seq = cn.ConvertNullInt(drc[0]["bgm_proj_seq"]);
                    info.emp_seq = cn.ConvertNullInt(drc[0]["emp_seq"]);
                    info.job_site_seq = cn.ConvertNullInt(drc[0]["job_site_seq"]);
                    info.sum_amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.attend_count = cn.ConvertNullInt(drc[0]["attend_count"]);

                    M_BGM_Project subInfo1 = new M_BGM_Project();
                    M_HRM_MP_Jobsites subInfo2 = new M_HRM_MP_Jobsites();
                    M_HRM_PS_Employees subInfo3 = new M_HRM_PS_Employees();

                    subInfo1.bgm_proj_name = drc[0]["bgm_proj_name"].ToString();
                    subInfo2.job_site_name = drc[0]["job_site_name"].ToString();
                    subInfo2.name_short = drc[0]["job_site_name_short"].ToString();
                    subInfo3.emp_name = drc[0]["emp_name"].ToString();

                    info.m_bgm_project = subInfo1;
                    info.m_hrm_mp_jobsites = subInfo2;
                    info.m_hrm_ps_employees = subInfo3;

                    info.record_status = drc[0]["record_status"].ToString();
                    info.record_status_name = drc[0]["record_status_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public bool HaveAmountExist(int bgm_proj_oper_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            bool bPass = true;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(1, "@delete_record_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select coalesce(opexp.amount, 0) as amount"
                    + ", coalesce(pjre.remain_amount, 0) as remain_amount"
                    + " from bgm_project_operation op"
                    + " left join (select opexp.bgm_proj_oper_seq, exp.bgm_exp_grp_seq"
                        + ", sum(coalesce(amount, 0)) as amount"
	                    + " from bgm_project_operation_expense opexp"
	                    + " left join bgm_project_expense pjexp on opexp.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
	                    + " left join bgm_mas_expense exp on pjexp.bgm_exp_seq = exp.bgm_exp_seq"
	                    + " group by opexp.bgm_proj_oper_seq, exp.bgm_exp_grp_seq"
	                    + ") opexp on op.bgm_proj_oper_seq = opexp.bgm_proj_oper_seq"
                    + " left join (select pjexp.bgm_proj_seq, exp.bgm_exp_grp_seq"
                        + ", sum(coalesce(pjre.cal_amount, 0)) as remain_amount"
	                    + " from bgm_project_report pjre"
	                    + " left join bgm_project_expense pjexp on pjre.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
	                    + " left join bgm_mas_expense exp on pjexp.bgm_exp_seq = exp.bgm_exp_seq"
                        + " where pjre.record_status not like @delete_record_status"
	                    + " group by pjexp.bgm_proj_seq, exp.bgm_exp_grp_seq"
	                    + ") pjre on op.bgm_proj_seq = pjre.bgm_proj_seq"
                        + " and opexp.bgm_exp_grp_seq = pjre.bgm_exp_grp_seq"
                    + " WHERE op.bgm_proj_oper_seq = @bgm_proj_oper_seq";
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                for (int i = 0; i < drc.Count; i++)
                {
                    double dBathAmount = Convert.ToDouble(drc[i]["amount"]);
                    double dRemainAmount = Convert.ToDouble(drc[i]["remain_amount"]);

                    if (dBathAmount > dRemainAmount)
                    {
                        bPass = false;
                        return bPass;
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return bPass;
        }

        public string Insert(M_BGM_ProjectOperation info)
        {
            string strSQL;
            string strSeq = "";
            string strNewCode = "";
            try
            {
                dbManager.Open();
                //dbManager.BeginTransaction();
                dbManager.CreateParameters(16);

                dbManager.AddParameters(0, "@bgm_proj_oper_seq", info.bgm_proj_oper_seq);
                dbManager.AddParameters(1, "@bgm_proj_oper_code", info.bgm_proj_oper_code == null ? DBNull.Value : (object)info.bgm_proj_oper_code);
                dbManager.AddParameters(2, "@bgm_proj_oper_name", info.bgm_proj_oper_name == null ? DBNull.Value : (object)info.bgm_proj_oper_name);
                dbManager.AddParameters(3, "@bgm_proj_oper_descr", info.bgm_proj_oper_descr == null ? DBNull.Value : (object)info.bgm_proj_oper_descr);
                dbManager.AddParameters(4, "@bgm_proj_oper_date", info.bgm_proj_oper_date == null ? DBNull.Value : (object)info.bgm_proj_oper_date);
                dbManager.AddParameters(5, "@start_date", info.start_date == null ? DBNull.Value : (object)info.start_date);
                dbManager.AddParameters(6, "@end_date", info.end_date == null ? DBNull.Value : (object)info.end_date);
                dbManager.AddParameters(7, "@bgm_year", info.bgm_year == null ? DBNull.Value : (object)info.bgm_year);
                dbManager.AddParameters(8, "@bgm_proj_seq", info.bgm_proj_seq == null ? DBNull.Value : (object)info.bgm_proj_seq);
                dbManager.AddParameters(9, "@emp_seq", info.emp_seq == null ? DBNull.Value : (object)info.emp_seq);
                dbManager.AddParameters(10, "@job_site_seq", info.job_site_seq == null ? DBNull.Value : (object)info.job_site_seq);
                dbManager.AddParameters(11, "@is_use", info.is_use == null ? DBNull.Value : (object)info.is_use);
                dbManager.AddParameters(12, "@attend_count", info.attend_count == null ? DBNull.Value : (object)info.attend_count);

                dbManager.AddParameters(13, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(14, "@created_by", info.created_by);
                dbManager.AddParameters(15, "@created_when", info.created_when);

                strSQL = "select op.bgm_proj_seq, coalesce(max(op.bgm_proj_oper_code), '0') as max_code, pj.bgm_proj_code"
                    + " from bgm_project_operation op"
                    + " left join bgm_project pj on op.bgm_proj_seq = pj.bgm_proj_seq"
                    + " where op.bgm_proj_seq = @bgm_proj_seq"
                    + " and op.record_status not like 'D'"
                    + " group by op.bgm_proj_seq, pj.bgm_proj_code";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0 && !string.IsNullOrEmpty(dtb.Rows[0]["max_code"].ToString()))
                {
                    strNewCode = dtb.Rows[0]["max_code"].ToString();
                    string[] strArray = strNewCode.Split('-');
                    string strRunning = "00";
                    int i;

                    if (strArray.Count() > 0)
                    {
                        strRunning = strArray[strArray.Count() - 1];
                    }

                    strRunning = (int.TryParse(strRunning, out i) ? Convert.ToInt32(strRunning) + 1 : 1).ToString("00");
                    strNewCode = dtb.Rows[0]["bgm_proj_code"].ToString() + "-" + strRunning;
                }

                strSQL = "INSERT INTO bgm_project_operation"
                    + "(bgm_proj_oper_code, bgm_proj_oper_name, bgm_proj_oper_descr, bgm_proj_oper_date"
                    + ", start_date, end_date, bgm_year, bgm_proj_seq, emp_seq, job_site_seq, is_use, attend_count"

                    + ", record_status, created_by, created_when)"
                    + " VALUES ('" + strNewCode + "'"
                        + ", @bgm_proj_oper_name"
                        + ", @bgm_proj_oper_descr"
                        + ", @bgm_proj_oper_date"
                        + ", @start_date"
                        + ", @end_date"
                        + ", @bgm_year"
                        + ", @bgm_proj_seq"
                        + ", @emp_seq"
                        + ", @job_site_seq"
                        + ", @is_use"
                        + ", @attend_count"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when"
                        + ");"
                        + " SELECT scope_identity();";
                strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                //dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                //strSQL = 
                //if (ds.Tables[0].Rows.Count > 0)
                //{
                //    strSeq = ds.Tables[0].Rows[0][0].ToString();
                //}
                //dbManager.CommitTransaction();

                return strSeq;
            }
            catch (Exception ex)
            {
                //dbManager.RollBackTransaction();
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_BGM_ProjectOperation info)
        {
            string strSQL;
            try
            {
                if (string.IsNullOrEmpty(info.bgm_proj_oper_code))
                {
                    info.bgm_proj_oper_code = GetInfo(info.bgm_proj_oper_seq).bgm_proj_oper_code;
                }

                dbManager.Open();
                dbManager.CreateParameters(16);

                dbManager.AddParameters(0, "@bgm_proj_oper_seq", info.bgm_proj_oper_seq);
                dbManager.AddParameters(1, "@bgm_proj_oper_code", info.bgm_proj_oper_code);
                dbManager.AddParameters(2, "@bgm_proj_oper_name", info.bgm_proj_oper_name);
                dbManager.AddParameters(3, "@bgm_proj_oper_descr", info.bgm_proj_oper_descr);
                dbManager.AddParameters(4, "@bgm_proj_oper_date", info.bgm_proj_oper_date);
                dbManager.AddParameters(5, "@start_date", info.start_date);
                dbManager.AddParameters(6, "@end_date", info.end_date);
                dbManager.AddParameters(7, "@bgm_year", info.bgm_year);
                dbManager.AddParameters(8, "@bgm_proj_seq", info.bgm_proj_seq);
                dbManager.AddParameters(9, "@emp_seq", info.emp_seq);
                dbManager.AddParameters(10, "@job_site_seq", info.job_site_seq);
                dbManager.AddParameters(11, "@is_use", info.is_use);
                dbManager.AddParameters(12, "@attend_count", info.attend_count);

                dbManager.AddParameters(13, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(14, "@updated_by", info.updated_by);
                dbManager.AddParameters(15, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_operation"
                    + " SET bgm_proj_oper_code = @bgm_proj_oper_code"
                    + ", bgm_proj_oper_name = @bgm_proj_oper_name"
                    + ", bgm_proj_oper_descr = @bgm_proj_oper_descr"
                    + ", bgm_proj_oper_date = @bgm_proj_oper_date"
                    + ", start_date = @start_date"
                    + ", end_date = @end_date"
                    + ", bgm_year = @bgm_year"
                    + ", bgm_proj_seq = @bgm_proj_seq"
                    + ", emp_seq = @emp_seq"
                    + ", job_site_seq = @job_site_seq"
                    + ", is_use = @is_use"
                    + ", attend_count = @attend_count"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_seq = @bgm_proj_oper_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_BGM_ProjectOperation info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@bgm_proj_oper_seq", info.bgm_proj_oper_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_operation"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_seq = @bgm_proj_oper_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "UPDATE bgm_project_operation_expense"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_seq = @bgm_proj_oper_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "UPDATE bgm_project_operation_person"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_seq = @bgm_proj_oper_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void ChangeStatus(M_BGM_ProjectOperation info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@bgm_proj_oper_seq", info.bgm_proj_oper_seq);
                dbManager.AddParameters(1, "@record_status", info.record_status);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_operation"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_seq = @bgm_proj_oper_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void CloseOperation(M_BGM_ProjectOperation info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@bgm_proj_oper_seq", info.bgm_proj_oper_seq);
                dbManager.AddParameters(1, "@record_status", info.record_status);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_operation"
                    + " SET is_use = 0"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_oper_seq = @bgm_proj_oper_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //public string SelectRecentInsertedRoleSeq()
        //{
        //    string strSQL;
        //    string strResult;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select scope_identity() as last_seq from bgm_project_operation";

        //        strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strResult;
        //}

        public DataTable Report_ProjectOperation(Nullable<int> seq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT op.bgm_proj_oper_seq, op.bgm_proj_oper_code"
                    + ", op.bgm_proj_oper_name"
                    + ", DATEADD(year, 543, op.bgm_proj_oper_date) as bgm_proj_oper_date"
                    + ", DATEADD(year, 543, op.start_date) as start_date"
                    + ", DATEADD(year, 543, op.end_date) as end_date"
                    + ", op.bgm_year, op.bgm_proj_seq"
                    + ", op.attend_count, op.work_place"

                    + ", pj.bgm_proj_name"
                    + ", strlist3.bgm_strat_list_name as output_name"
                    + ", strlist4.bgm_strat_list_name as department_goal_name"
                    //+ ", opt.bgm_option_name"

                    + " FROM bgm_project_operation op"

                    + " LEFT JOIN bgm_project pj ON op.bgm_proj_seq = pj.bgm_proj_seq"
                    + " LEFT JOIN bgm_strategy_data_mapping strmap1 ON pj.bgm_strat_list_seq = strmap1.bgm_strat_list_seq"
                    + " LEFT JOIN bgm_strategy_data_mapping strmap2 ON strmap1.upper_bgm_strat_data_map_seq = strmap2.bgm_strat_data_map_seq"
                    + " LEFT JOIN bgm_strategy_data_mapping strmap3 ON strmap2.upper_bgm_strat_data_map_seq = strmap3.bgm_strat_data_map_seq"
                    + " LEFT JOIN bgm_strategy_data_mapping strmap4 ON strmap3.upper_bgm_strat_data_map_seq = strmap4.bgm_strat_data_map_seq"
                    + " LEFT JOIN bgm_strategy_data_list strlist3 ON strmap3.bgm_strat_list_seq = strlist3.bgm_strat_list_seq"
                    + " LEFT JOIN bgm_strategy_data_list strlist4 ON strmap4.bgm_strat_list_seq = strlist4.bgm_strat_list_seq"
                    //+ " LEFT JOIN (SELECT pjopt.bgm_proj_seq, opt.bgm_option_name"
                    //  + " FROM bgm_project_option pjopt"
                    //  + " LEFT JOIN bgm_mas_option opt ON pjopt.bgm_option_seq = opt.bgm_option_seq"
                    //  + " WHERE opt.bgm_option_type_seq = 1"
                    //  + ") opt ON pj.bgm_proj_seq = opt.bgm_proj_seq"
                    + " WHERE op.bgm_proj_oper_seq = @seq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable SubReport_ProjectOperation(Nullable<int> seq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_record_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT exp1.bgm_exp_seq, exp1.bgm_exp_code, exp1.bgm_exp_name"
                    + ", exp2.bgm_exp_seq as bgm_exp_seq2"
                    + ", exp2.bgm_exp_code as bgm_exp_code2"
                    + ", exp2.bgm_exp_name as bgm_exp_name2"
                    + ", expgrp.bgm_exp_grp_seq, expgrp.bgm_exp_grp_code, expgrp.bgm_exp_grp_name"
                    + ", strlist.bgm_strat_list_seq as subact_seq"
                    + ", strlist.bgm_strat_list_code as subact_code"
                    + ", strlist.bgm_strat_list_name as subact_name"
                    + ", opexp.amount"
                    + " FROM bgm_project_operation_expense opexp"
                    + " LEFT JOIN bgm_project_expense pjexp ON opexp.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp1 ON pjexp.bgm_exp_seq  = exp1.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp2 ON exp1.upper_bgm_exp_seq  = exp2.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense_group expgrp ON exp1.bgm_exp_grp_seq = expgrp.bgm_exp_grp_seq"
                    + " LEFT JOIN bgm_project pj ON pjexp.bgm_proj_seq = pj.bgm_proj_seq"
                    + " LEFT JOIN bgm_strategy_data_list strlist ON pj.bgm_strat_list_seq = strlist.bgm_strat_list_seq"
                    + " WHERE opexp.bgm_proj_oper_seq = @seq"
                    + " AND opexp.record_status not like @delete_record_status";


                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable Report_ProjectOperationEdit(Nullable<int> seq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT edit.bgm_proj_oper_edit_seq as bgm_proj_oper_seq"
                    + ", edit.bgm_proj_oper_edit_code as bgm_proj_oper_code"
                    + ", op.bgm_proj_oper_name"
                    + ", DATEADD(year, 543, edit.bgm_proj_oper_edit_date) as bgm_proj_oper_date"
                    + ", DATEADD(year, 543, op.start_date) as start_date"
                    + ", DATEADD(year, 543, op.end_date) as end_date"
                    + ", op.bgm_year, op.bgm_proj_seq"
                    + ", op.attend_count, op.work_place"

                    + ", pj.bgm_proj_name"
                    + ", strlist3.bgm_strat_list_name as output_name"
                    + ", strlist4.bgm_strat_list_name as department_goal_name"

                    + " FROM bgm_project_operation_edit edit"
                    + " left join bgm_project_operation op on edit.bgm_proj_oper_seq = op.bgm_proj_oper_seq"
                    + " LEFT JOIN bgm_project pj ON op.bgm_proj_seq = pj.bgm_proj_seq"
                    + " LEFT JOIN bgm_strategy_data_mapping strmap1 ON pj.bgm_strat_list_seq = strmap1.bgm_strat_list_seq"
                    + " LEFT JOIN bgm_strategy_data_mapping strmap2 ON strmap1.upper_bgm_strat_data_map_seq = strmap2.bgm_strat_data_map_seq"
                    + " LEFT JOIN bgm_strategy_data_mapping strmap3 ON strmap2.upper_bgm_strat_data_map_seq = strmap3.bgm_strat_data_map_seq"
                    + " LEFT JOIN bgm_strategy_data_mapping strmap4 ON strmap3.upper_bgm_strat_data_map_seq = strmap4.bgm_strat_data_map_seq"
                    + " LEFT JOIN bgm_strategy_data_list strlist3 ON strmap3.bgm_strat_list_seq = strlist3.bgm_strat_list_seq"
                    + " LEFT JOIN bgm_strategy_data_list strlist4 ON strmap4.bgm_strat_list_seq = strlist4.bgm_strat_list_seq"
                    + " WHERE edit.bgm_proj_oper_edit_seq = @seq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable SubReport_ProjectOperationEdit(Nullable<int> seq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_record_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT exp1.bgm_exp_seq, exp1.bgm_exp_code, exp1.bgm_exp_name"
                    + ", exp2.bgm_exp_seq as bgm_exp_seq2"
                    + ", exp2.bgm_exp_code as bgm_exp_code2"
                    + ", exp2.bgm_exp_name as bgm_exp_name2"
                    + ", expgrp.bgm_exp_grp_seq, expgrp.bgm_exp_grp_code, expgrp.bgm_exp_grp_name"
                    + ", strlist.bgm_strat_list_seq as subact_seq"
                    + ", strlist.bgm_strat_list_code as subact_code"
                    + ", strlist.bgm_strat_list_name as subact_name"
                    + ", editexp.add_amount as amount"
                    + " FROM bgm_project_operation_edit_expense editexp"
                    + " left join bgm_project_operation_expense opexp on editexp.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                    + " LEFT JOIN bgm_project_expense pjexp ON opexp.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp1 ON pjexp.bgm_exp_seq  = exp1.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense exp2 ON exp1.upper_bgm_exp_seq  = exp2.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense_group expgrp ON exp1.bgm_exp_grp_seq = expgrp.bgm_exp_grp_seq"
                    + " LEFT JOIN bgm_project pj ON pjexp.bgm_proj_seq = pj.bgm_proj_seq"
                    + " LEFT JOIN bgm_strategy_data_list strlist ON pj.bgm_strat_list_seq = strlist.bgm_strat_list_seq"
                    + " WHERE editexp.bgm_proj_oper_edit_seq = @seq";


                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public string GetOptionProduct(Nullable<int> seq)
        {
            string strSQL;
            string strResult = "";
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_record_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT coalesce(opt.bgm_option_name, '-') as bgm_option_name, op.bgm_proj_oper_seq"

                    + " FROM bgm_project_operation op"
                    + " LEFT JOIN bgm_project pj ON op.bgm_proj_seq = pj.bgm_proj_seq"
                    + " LEFT JOIN (SELECT pjopt.bgm_proj_seq, opt.bgm_option_name"
                      + " FROM bgm_project_option pjopt"
                      + " LEFT JOIN bgm_mas_option opt ON pjopt.bgm_option_seq = opt.bgm_option_seq"
                      + " WHERE opt.bgm_option_type_seq = 1"
                      + ") opt ON pj.bgm_proj_seq = opt.bgm_proj_seq"
                    + " WHERE op.bgm_proj_oper_seq = @seq";


                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                for (int i = 0; i < dtb.Rows.Count; i++)
                {
                    if (i != 0)
                    {
                        strResult += ", ";
                    }

                    strResult += dtb.Rows[i]["bgm_option_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return strResult;
        }

        public string ProjectOprationCode(string strBgmProjOperSeq)
        {
            string strSQL;
            string ProjOperCode;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@bgm_proj_oper_seq", strBgmProjOperSeq);
                dbManager.AddParameters(1, "@delete_record_status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT bgm_proj_oper_code FROM bgm_project_operation "
                         + " WHERE bgm_proj_oper_seq = @bgm_proj_oper_seq AND record_status NOT LIKE @delete_record_status";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                ProjOperCode = Convert.ToString(dtb.Rows[0][0]);
                return ProjOperCode;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
